package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import arq.SigcfcArq;

import play.db.DB;
import models.Aluno;
import models.Professor;
import utils.exceptions.BDException;


public class ProfessorDAO{
	Connection conexao;
	
	public ProfessorDAO() {
		conexao = DB.getConnection();
	}
	
	public boolean inserir(Professor _professor) throws BDException {
		boolean retorno = false;
		String comando = "insert into professor (cpf, nome, nascimento, rg, cep, cidade, estado, rua, numero, bairro, complemento, telefone1, telefone2, email, login, senha, cnh, exp_cnh)"
			+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	try {
		PreparedStatement stmt = conexao.prepareStatement(comando);

		stmt.setLong(1, _professor.getCpf());
		stmt.setString(2, _professor.getNome());
		stmt.setDate(3, SigcfcArq.converterDateUtil2Sql(_professor.getDataNascimento()));
		stmt.setLong(4, _professor.getRg());
		stmt.setLong(5, _professor.getCep());
		stmt.setString(6, _professor.getCidade());
		stmt.setString(7, _professor.getEstado());
		stmt.setString(8, _professor.getRua());
		stmt.setInt(9, _professor.getNumero());
		stmt.setString(10, _professor.getBairro());
		stmt.setString(11, _professor.getComplemento());
		stmt.setLong(12, _professor.getTelefone1());
		stmt.setLong(13, _professor.getTelefone2());
		stmt.setString(14, _professor.getEmail());
		stmt.setString(15, _professor.getLogin());
		stmt.setString(16, _professor.getSenha());
		stmt.setLong(17, _professor.getCnh());
		stmt.setDate(18, SigcfcArq.converterDateUtil2Sql(_professor.getExpCnh()));

		stmt.execute();
		stmt.close();
		retorno = true;
	} catch (SQLException e) {
		
		throw new BDException("Ocorreu ao INSERIR o Professor.",e.getStackTrace());
	}
	
	return retorno;
	}

	public boolean editar(Professor _professor) throws BDException{
		
		boolean retorno = false;
		

		String comando = "update professor set cpf=?, nome=?, nascimento=?, rg=?, cep=?, cidade=?, estado=?, rua=?, numero=?, bairro=?, complemento=?, telefone1=?, telefone2=?, email=?, login=?, senha=?, cnh=?, exp_cnh=? where idprofessor=?";
		
		try {
			PreparedStatement stmt = conexao.prepareStatement(comando);

			
			stmt.setLong(1, _professor.getCpf());
			stmt.setString(2, _professor.getNome());
			stmt.setDate(3, SigcfcArq.converterDateUtil2Sql(_professor.getDataNascimento()));
			stmt.setLong(4, _professor.getRg());
			stmt.setLong(5, _professor.getCep());
			stmt.setString(6, _professor.getCidade());
			stmt.setString(7, _professor.getEstado());
			stmt.setString(8, _professor.getRua());
			stmt.setInt(9, _professor.getNumero());
			stmt.setString(10, _professor.getBairro());
			stmt.setString(11, _professor.getComplemento());
			stmt.setLong(12, _professor.getTelefone1());
			stmt.setLong(13, _professor.getTelefone2());
			stmt.setString(14, _professor.getEmail());
			stmt.setString(15, _professor.getLogin());
			stmt.setString(16, _professor.getSenha());
			stmt.setLong(17, _professor.getCnh());
			stmt.setDate(18, SigcfcArq.converterDateUtil2Sql(_professor.getExpCnh()));
			stmt.setLong(19, _professor.getIdProfessor());
			stmt.execute();
			stmt.close();
			
			retorno = true;
		} catch (SQLException e) {
			
			throw new BDException("Ocorreu ao EDITAR o Professor.",e.getStackTrace());
		}

		return retorno;

	}

	public List<Professor> listarTodos() throws BDException {
		List<Professor> retorno = new ArrayList<Professor>();
		String query = "select * from professor order by nome;";
		
		try {
			Statement stmt = conexao.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			
			while (rs.next()){
				Professor professor = new Professor();
				
				professor.setIdProfessor(rs.getLong("idprofessor"));
				professor.setCpf(rs.getLong("cpf"));
				professor.setNome(rs.getString("nome"));
				professor.setRg(rs.getLong("rg"));
				professor.setDataNascimento(SigcfcArq.converterDateSql2Util(rs.getDate("nascimento")));
				professor.setBairro(rs.getString("bairro"));
				professor.setCep(rs.getLong("cep"));
				professor.setCidade(rs.getString("cidade"));
				professor.setComplemento(rs.getString("complemento"));				
				professor.setEmail(rs.getString("email"));
				professor.setEstado(rs.getString("estado"));
				professor.setLogin(rs.getString("login"));				
				professor.setNumero(rs.getInt("numero"));
				professor.setRua(rs.getString("rua"));
				professor.setSenha(rs.getString("senha"));
				professor.setTelefone1(rs.getLong("telefone1"));
				professor.setTelefone2(rs.getLong("telefone2"));
				professor.setCnh(rs.getLong("cnh"));
				professor.setExpCnh(SigcfcArq.converterDateSql2Util(rs.getDate("exp_cnh")));
				retorno.add(professor);
			}
			stmt.close();
		} catch (SQLException e) {
			
			throw new BDException("Ocorreu ao LISTAR os Professores.",e.getStackTrace());
		} 
		
		return retorno;
	}

	public boolean excluir(long _cpf) throws BDException {
		boolean retorno = false;

		String comando = "delete from professor where cpf=?;";
		try {
			PreparedStatement stmt = conexao.prepareStatement(comando);

			stmt.setLong(1, _cpf);
			
			stmt.execute();
			stmt.close();
			retorno = true;
		} catch (SQLException e) {
			
			throw new BDException("Ocorreu erro ao EXCLUIR o Professor.",e.getStackTrace());
		}

		return retorno;
	}

	public Professor buscarProfessor(long _cpf) throws BDException {
		String comando = "select * from professor where cpf='"+_cpf+"'";

		Professor professor = new Professor();
		try {
			java.sql.Statement stmt = conexao.createStatement();
			ResultSet rs = stmt.executeQuery(comando);

			while (rs.next()) {

				professor.setIdProfessor(rs.getLong("idprofessor"));
				professor.setCpf(rs.getLong("cpf"));
				professor.setNome(rs.getString("nome"));
				professor.setRg(rs.getLong("rg"));
				professor.setDataNascimento(SigcfcArq.converterDateSql2Util(rs.getDate("nascimento")));
				professor.setCep(rs.getLong("cep"));
				professor.setCidade(rs.getString("cidade"));
				professor.setEstado(rs.getString("estado"));
				professor.setRua(rs.getString("rua"));
				professor.setNumero(rs.getInt("numero"));
				professor.setBairro(rs.getString("bairro"));
				professor.setComplemento(rs.getString("complemento"));
				professor.setTelefone1(rs.getLong("telefone1"));
				professor.setTelefone2(rs.getLong("telefone2"));
				professor.setEmail(rs.getString("email"));
				professor.setLogin(rs.getString("login"));
				professor.setSenha(rs.getString("senha"));
				professor.setCnh(rs.getLong("cnh"));
				professor.setExpCnh(SigcfcArq.converterDateSql2Util(rs.getDate("exp_cnh")));

			}
			stmt.close();

		} catch (SQLException e) {
			
			throw new BDException("Ocorreu erro ao BUSCAR o Professor.",e.getStackTrace());
		}
		return professor;
	}
	
	//buscar professor pelo nome
	public Professor buscarProfessor(String nome) throws BDException {
		String comando = "select * from professor where nome='"+nome+"'";

		Professor professor = new Professor();
		try {
			java.sql.Statement stmt = conexao.createStatement();
			ResultSet rs = stmt.executeQuery(comando);

			while (rs.next()) {

				professor.setIdProfessor(rs.getLong("idprofessor"));
				professor.setCpf(rs.getLong("cpf"));
				professor.setNome(rs.getString("nome"));
				professor.setRg(rs.getLong("rg"));
				professor.setDataNascimento(SigcfcArq.converterDateSql2Util(rs.getDate("nascimento")));
				professor.setCep(rs.getLong("cep"));
				professor.setCidade(rs.getString("cidade"));
				professor.setEstado(rs.getString("estado"));
				professor.setRua(rs.getString("rua"));
				professor.setNumero(rs.getInt("numero"));
				professor.setBairro(rs.getString("bairro"));
				professor.setComplemento(rs.getString("complemento"));
				professor.setTelefone1(rs.getLong("telefone1"));
				professor.setTelefone2(rs.getLong("telefone2"));
				professor.setEmail(rs.getString("email"));
				professor.setLogin(rs.getString("login"));
				professor.setSenha(rs.getString("senha"));
				professor.setCnh(rs.getLong("cnh"));
				professor.setExpCnh(SigcfcArq.converterDateSql2Util(rs.getDate("exp_cnh")));

			}
			stmt.close();

		} catch (SQLException e) {
			
			throw new BDException("Ocorreu erro ao BUSCAR o Professor.",e.getStackTrace());
		}
		return professor;
	}
	
	public Professor buscarProfessorLogin(String login, String senha) throws BDException {		
		Professor prof = new Professor();		
		String comando = "select * from professor where login = '"+login+"' and senha = '"+senha+"'";				
		try {
			java.sql.Statement start = conexao.createStatement();
			ResultSet rs = start.executeQuery(comando);			
			while(rs.next()){	
				prof.setIdProfessor(rs.getLong("idprofessor"));
				prof.setNome(rs.getString("nome"));
				prof.setCpf(rs.getLong("cpf"));
				prof.setLogin(rs.getString("login"));
				prof.setSenha(rs.getString("senha"));						
			}			
		} catch (SQLException e) {
			throw new BDException("Ocorreu erro na hora de BUSCAR a Adm (try catch)",e.getStackTrace());
		}		
		return prof;
	}
}
